package org.imixs.manik.webstat.ejb;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.logging.Logger;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.imixs.manik.webstat.jpa.Analyzer;

/**
 * This EJB implements the business logic to manage the Analyzer entity objects.
 * 
 * @author rsoika
 * 
 */
@Stateless
public class AnalyzerService {

	@PersistenceContext(unitName = "org.imixs.manik.webstat.jpa")
	EntityManager em;

	private static Logger logger = Logger.getLogger("org.imixs.manik.webstat");

	public void save(Analyzer currentAnalyzer) throws Exception {
		// save domain now...

		if (currentAnalyzer.getId() == null) {
			em.persist(currentAnalyzer);
			return;

		}
		Analyzer oldDomain = em.find(Analyzer.class, currentAnalyzer.getId());
		if (oldDomain == null) {

			em.persist(currentAnalyzer);
		} else {

			em.merge(currentAnalyzer);
		}
	}

	public void remove(Analyzer currentAnalyzer) throws Exception {

		logger.info("remove id=" + currentAnalyzer.getId());
		Analyzer oldDomain = em.find(Analyzer.class, currentAnalyzer.getId());
		if (oldDomain != null) {
			em.remove(oldDomain);
		} else
			logger.info("Analzyer object not found");

	}

	public List<Analyzer> findAllAnalyzers() {
		List<Analyzer> analyzerlist = new ArrayList<Analyzer>();
		Collection<Analyzer> col = null;
		try {
			Query q = em.createQuery("SELECT analyzer FROM Analyzer analyzer");
			col = q.getResultList();
			for (Analyzer aitem : col) {
				analyzerlist.add(aitem);
			}
		} catch (Exception ee) {
			ee.printStackTrace();
		}

		return analyzerlist;

	}

	/**
	 * Computes the total hits grouped by request URI
	 * 
	 * @return
	 */
	public List<ArrayList<Object>> computeTotalHitsRequestURI(
			Analyzer analyzer, int startpos, int count) {

		if (analyzer == null || analyzer.getAccessLog() == null)
			return new ArrayList<ArrayList<Object>>();

		String sQuery = "SELECT ae.request_uri, COUNT(ae) FROM AccessEntity ae"
				+ "  WHERE " + getStandardFilterJPQL(analyzer, "ae", true);
		sQuery += " AND ae.statuscode<'400' GROUP BY ae.request_uri "
				+ " ORDER BY COUNT(ae) DESC";

		return queryResult(sQuery, startpos, count);

	}

	/**
	 * Computes the total hits grouped by referrer URI
	 * 
	 * Domains specified in the Analyzer will be ignored.
	 * 
	 * @return
	 */
	public List<ArrayList<Object>> computeTotalHitsReferrer(Analyzer analyzer,
			int startpos, int count) {
		if (analyzer == null || analyzer.getAccessLog() == null)
			return new ArrayList<ArrayList<Object>>();

		String sQuery = "SELECT ae.referrer, COUNT(ae) FROM AccessEntity ae"
				+ "  WHERE " + getStandardFilterJPQL(analyzer, "ae", true);

		// add filter
		String sDomins = analyzer.getDomains();
		if (sDomins != null && !"".equals(sDomins)) {
			// split Filter into tokens
			String[] filters = sDomins.split("\n");

			sQuery += " AND NOT (";
			for (int i = 0; i < filters.length; i++) {
				String aFilter = filters[i];
				aFilter = aFilter.replace("\r", "");
				// replace * with %
				aFilter = aFilter.replace('*', '%');
				if (!aFilter.startsWith("%"))
					aFilter = "%" + aFilter;
				if (!aFilter.endsWith("%"))
					aFilter = aFilter + "%";
				sQuery += "ae.referrer LIKE '" + aFilter + "'";
				if (i < filters.length - 1)
					sQuery += " OR ";
			}
			sQuery += ") ";
		}

		sQuery += " AND ae.statuscode<'400' GROUP BY ae.referrer "
				+ " ORDER BY COUNT(ae) DESC";

		logger.info(sQuery);

		return queryResult(sQuery, startpos, count);
	}

	/**
	 * Computes trend by day
	 * 
	 * @return
	 */
	public List<ArrayList<Object>> computeTrendByDay(Analyzer analyzer,
			int month, int year) {
		if (analyzer == null || analyzer.getAccessLog() == null)
			return new ArrayList<ArrayList<Object>>();

		// compute month
		Calendar calFrom = Calendar.getInstance();
		Calendar calTo = Calendar.getInstance();
		// set time
		calFrom.set(Calendar.HOUR_OF_DAY, 0);
		calFrom.set(Calendar.MINUTE, 0);
		calFrom.set(Calendar.SECOND, 0);
		calTo.set(Calendar.HOUR_OF_DAY, 23);
		calTo.set(Calendar.MINUTE, 59);
		calTo.set(Calendar.SECOND, 59);

		calFrom.set(Calendar.DAY_OF_MONTH, 1);
		calFrom.set(Calendar.MONTH, month);
		calFrom.set(Calendar.YEAR, year);

		calTo.set(Calendar.DAY_OF_MONTH, 1);
		calTo.set(Calendar.MONTH, month + 1);
		calTo.set(Calendar.YEAR, year);
		calTo.add(Calendar.DAY_OF_MONTH, -1);

		String sQuery = "SELECT ae.dayofmonth, COUNT(ae) FROM AccessEntity ae"
				+ "  WHERE " + getStandardFilterJPQL(analyzer, "ae", false);

		DateFormat sqlFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		sQuery += "AND (ae.datetime>='" + sqlFormat.format(calFrom.getTime()) + "') ";

		sQuery += "AND (ae.datetime<='" + sqlFormat.format(calTo.getTime()) + "') ";

		sQuery += " AND ae.statuscode<'400' GROUP BY ae.dayofmonth "
				+ " ORDER BY ae.datetime ASC";

		logger.info("computeTrendByDay Query="+sQuery);

		return queryResult(sQuery, 0, 0);
	}
	
	/**
	 * Computes trend by day
	 * 
	 * @return
	 */
	public List<ArrayList<Object>> computeTrendByMonth(Analyzer analyzer,int year) {
		if (analyzer == null || analyzer.getAccessLog() == null)
			return new ArrayList<ArrayList<Object>>();

		// compute month
		Calendar calFrom = Calendar.getInstance();
		Calendar calTo = Calendar.getInstance();
		// set time
		calFrom.set(Calendar.HOUR_OF_DAY, 0);
		calFrom.set(Calendar.MINUTE, 0);
		calFrom.set(Calendar.SECOND, 0);
		calTo.set(Calendar.HOUR_OF_DAY, 23);
		calTo.set(Calendar.MINUTE, 59);
		calTo.set(Calendar.SECOND, 59);

		calFrom.set(Calendar.DAY_OF_MONTH, 1);
		calFrom.set(Calendar.MONTH, 0);
		calFrom.set(Calendar.YEAR, year);

		calTo.set(Calendar.DAY_OF_MONTH, 1);
		calTo.set(Calendar.MONTH, 11);
		calTo.set(Calendar.YEAR, year);
		calTo.add(Calendar.DAY_OF_MONTH, 31);

		String sQuery = "SELECT ae.month, COUNT(ae) FROM AccessEntity ae"
				+ "  WHERE " + getStandardFilterJPQL(analyzer, "ae", false);

		DateFormat sqlFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		sQuery += "AND (ae.datetime>='" + sqlFormat.format(calFrom.getTime()) + "') ";

		sQuery += "AND (ae.datetime<='" + sqlFormat.format(calTo.getTime()) + "') ";

		sQuery += " AND ae.statuscode<'400' GROUP BY ae.month "
				+ " ORDER BY ae.datetime ASC";

		logger.info("computeTrendByMonth Query="+sQuery);

		return queryResult(sQuery, 0, 0);
	}

	/**
	 * Computes the total errors grouped by error code
	 * 
	 * @return
	 */
	public List<ArrayList<Object>> computeTotalErrors(Analyzer analyzer) {
		if (analyzer == null || analyzer.getAccessLog() == null)
			return new ArrayList<ArrayList<Object>>();

		String sQuery = "SELECT ae.statuscode, COUNT(ae) FROM AccessEntity ae"
				+ "  WHERE " + getStandardFilterJPQL(analyzer, "ae", true);

		sQuery += " AND ae.statuscode>='400'  GROUP BY ae.statuscode "
				+ "ORDER BY COUNT(ae) DESC";

		return queryResult(sQuery, 0, 0);
	}

	/**
	 * Computes the total error grouped by request for a specific error code
	 * 
	 * @return
	 */
	public List<ArrayList<Object>> computeTotalErrorsByCode(Analyzer analyzer,
			String error) {
		if (analyzer == null || analyzer.getAccessLog() == null)
			return new ArrayList<ArrayList<Object>>();

		if (error == null || "".equals(error))
			return new ArrayList<ArrayList<Object>>();

		String sQuery = "SELECT ae.request_uri,ae.referrer, COUNT(ae) FROM AccessEntity ae"
				+ "  WHERE " + getStandardFilterJPQL(analyzer, "ae", true);
		sQuery += " AND ae.statuscode='" + error
				+ "'  GROUP BY ae.request_uri,ae.referrer " + "ORDER BY COUNT(ae) DESC";

		return queryResult(sQuery, 0, 0);
	}

	/**
	 * Computes the % of browser type
	 * 
	 * @return
	 */
	public List<ArrayList<Object>> computeBrowserType(Analyzer analyzer) {
		if (analyzer == null || analyzer.getAccessLog() == null)
			return new ArrayList<ArrayList<Object>>();

		String sQuery = "SELECT ae.useragent_browsertype, COUNT(ae) FROM AccessEntity ae"
				+ "  WHERE " + getStandardFilterJPQL(analyzer, "ae", true);
		sQuery += " AND ae.statuscode<'400' GROUP BY ae.useragent_browsertype "
				+ " ORDER BY COUNT(ae) DESC";
		return queryResult(sQuery, 0, 0);
	}

	public List<ArrayList<Object>> computeBrowser(Analyzer analyzer) {
		if (analyzer == null || analyzer.getAccessLog() == null)
			return new ArrayList<ArrayList<Object>>();

		String sQuery = "SELECT ae.useragent_browser, COUNT(ae) FROM AccessEntity ae"
				+ "  WHERE " + getStandardFilterJPQL(analyzer, "ae", true);
		sQuery += " AND ae.statuscode<'400' GROUP BY ae.useragent_browser "
				+ " ORDER BY COUNT(ae) DESC";
		return queryResult(sQuery, 0, 0);
	}

	public List<ArrayList<Object>> computeOperatingsystem(Analyzer analyzer) {
		if (analyzer == null || analyzer.getAccessLog() == null)
			return new ArrayList<ArrayList<Object>>();

		String sQuery = "SELECT ae.useragent_operatingsystem, COUNT(ae) FROM AccessEntity ae"
				+ "  WHERE " + getStandardFilterJPQL(analyzer, "ae", true);
		sQuery += " AND ae.statuscode<'400' GROUP BY ae.useragent_operatingsystem "
				+ " ORDER BY COUNT(ae) DESC";
		return queryResult(sQuery, 0, 0);
	}

	/**
	 * returns a result list of a given JPQL statement. The result list contains
	 * ArrayList with the column values. So accessing those values can be done
	 * in JSF with a statment like this
	 * <p>
	 * <code>
	 * 	<rich:dataTable columns="2" 
			value="#{analyzerMB.totalHitsRequestURI}" var="record">
			<rich:column>
				<h:outputText value="#{record[0]}" />
			</rich:column>
			<rich:column>
				<h:outputText value="#{record[1]}" />
			</rich:column>
		</rich:dataTable>
	 * </code>
	 * 
	 * 
	 * @return
	 */
	public List<ArrayList<Object>> queryResult(String jpql, int startpos,
			int count) {
		List<ArrayList<Object>> analyzerlist = new ArrayList<ArrayList<Object>>();
		Collection<Object> col = null;
		try {

			Query q = em.createQuery(jpql);
			q.setMaxResults(count);
			q.setFirstResult(startpos);
			col = q.getResultList();
			for (Object aitem : col) {
				HashMap map = new HashMap();
				// printResult(aitem);
				if (aitem instanceof Object[]) {
					// logger.info("read result row");
					Object[] row = (Object[]) aitem;
					analyzerlist.add(new ArrayList<Object>(Arrays.asList(row)));
				}
			}
		} catch (Exception ee) {
			ee.printStackTrace();
		}

		return analyzerlist;

	}

	/**
	 * Creates a JPQL fragment including the filter and exclude setting.
	 * 
	 * @param analyzer
	 *            the Analyzer Object holding the Filter and Exclude setting
	 * @param entity
	 *            the name of the Entity to be used in the JQPL fragment
	 * @return a JPQL fragment
	 */
	private String getStandardFilterJPQL(Analyzer analyzer, String entity,
			boolean filterTimeFrame) {
		String sJPQL = "";
		String sFilterList;

		sJPQL = " " + entity + ".accessLog.id='"
				+ analyzer.getAccessLog().getId() + "' ";

		// add timerange
		if (filterTimeFrame) {
			DateFormat sqlFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			if (analyzer.getTimeFrom() != null)
				sJPQL += "AND (" + entity + ".datetime>='"
						+ sqlFormat.format(analyzer.getTimeFrom()) + "') ";
			if (analyzer.getTimeTo() != null)
				sJPQL += "AND (" + entity + ".datetime<='"
						+ sqlFormat.format(analyzer.getTimeTo()) + "') ";
		}

		// add filter
		sFilterList = analyzer.getFilter();
		if (sFilterList != null && !"".equals(sFilterList)) {
			// split Filter into tokens
			String[] filters = sFilterList.split("\n");

			sJPQL += "AND (";
			for (int i = 0; i < filters.length; i++) {
				String aFilter = filters[i];
				
				aFilter = aFilter.replace("\r", "");
				// replace * with %
				aFilter = aFilter.replace('*', '%');
				sJPQL += entity + ".request_uri LIKE '" + aFilter + "'";
				if (i < filters.length - 1)
					sJPQL += " OR ";
			}
			sJPQL += ") ";
		}

		// add exclude
		sFilterList = analyzer.getExclude();
		if (sFilterList != null && !"".equals(sFilterList)) {
			// split Filter into tokens
			String[] filters = sFilterList.split("\n");

			sJPQL += "AND NOT (";
			for (int i = 0; i < filters.length; i++) {
				String aFilter = filters[i];
				aFilter = aFilter.replace("\r", "");
				// replace * with %
				aFilter = aFilter.replace('*', '%');
				sJPQL += entity + ".request_uri LIKE '" + aFilter + "'";
				if (i < filters.length - 1)
					sJPQL += " OR ";
			}
			sJPQL += ") ";
		}
		logger.fine(" JPQL Filter = " + sJPQL);
		return sJPQL;
	}

	/**
	 * helper method to test query result
	 * 
	 * @param result
	 */
	private void printResult(Object result) {

		if (result == null)
			logger.info("NULL");
		else {
			if (result instanceof Object[]) {
				Object[] row = (Object[]) result;

				for (int i = 0; i < row.length; i++) {
					logger.info("Row-Value: " + row[i]);

				}

			} else
				logger.info("result=" + result);

		}
	}

}
